package day03;

import org.junit.Test;
import utilspackage.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author Xiao
 * @date 2021/8/20
 */
public class Homework3 {
    /**
     * 1. 定义JDBC工具类
     * 	1. 使用静态代码块初始化连接池对象 DataSource
     * 	2. 提供静态方法，从连接池中获取连接对象
     *
     * 2. 根据上述User表的字段描述，定义Users表。
     *
     * 3. 使用JDBC完成对Users表的增删改查
     */

    // 增
    @Test
    public void testInsert() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "INSERT INTO users (username, image, signature, nickname) VALUES " +
                "('阿良', 'wow...', '善良', '不擅长')," +
                "('左右', 'emm...', '为难', '五月之星')," +
                "('平安', 'hhh...', '宁姚', '苏格')," +
                "('小扁担', 'yeah...', '小板凳', '翁小祥')," +
                "('大水怪', 'aha...', '大糊涂', 'Luffyjun')";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        if (count > 0) {
            System.out.println("添加成功。。。");
        } else {
            System.out.println("添加失败。。。");
        }
        stmt.close();
        conn.close();
    }

    // 删
    @Test
    public void testDelete() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "DELETE FROM users WHERE id = 5";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        if (count > 0) {
            System.out.println("删除成功。。。");
        } else {
            System.out.println("删除失败。。。");
        }
        stmt.close();
        conn.close();
    }

    // 改
    @Test
    public void testUpdate() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "UPDATE users SET nickname = '大水怪' WHERE id = 4";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        if (count >= 0) {
            System.out.println("修改成功。。。");
        } else {
            System.out.println("修改失败。。。");
        }
        stmt.close();
        conn.close();
    }

    // 查
    @Test
    public void testSelect() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT id, username, nickname, image FROM users ORDER BY id";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt(1);
            String username = rs.getString(2);
            String nickname = rs.getString(3);
            String image = rs.getString(4);
            System.out.print(id + "\t" + username + "\t");
            System.out.println(nickname + "\t" + image);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }
}
